import pymysql
from pymysql.cursors import SSDictCursor, SSCursor, DictCursor

# class Database:
#     def __init__(self):     # 构造方法
#         self.conn = pymysql.connect(host='localhost', user='root', password='123456', database='woniusale', charset='utf8')
#         # self.cursor = self.conn.cursor()    # 创建一个默认的游标类型（返回二维元组）
#         self.cursor = self.conn.cursor(DictCursor)
#
#     def query_one(self, sql):
#         self.cursor.execute(sql)
#         result = self.cursor.fetchone()
#         return result
#
#     def query_all(self, sql):
#         self.cursor.execute(sql)
#         result = self.cursor.fetchall()
#         return result
#
#
#     def execute(self, sql):
#         self.cursor.execute(sql)
#         self.conn.commit()
#         return 'done'
#
#     def __del__(self):  # 析构方法
#         self.conn.close()
#         self.cursor.close()


import pymysql
from pymysql.cursors import DictCursor, SSDictCursor, SSCursor


class Database:
    def __init__(self):
        self.conn = pymysql.connect(host='47.103.31.116',
                                    user='root',
                                    password='Yangnan@112200',
                                    database='blogs',
                                    charset='utf8')
        # 使用SSDictCursor的游标类型来获取数据，数据库返回的数据将会是[{},{},{}]
        self.cursor = self.conn.cursor(DictCursor)

    # 查询一条
    def query_one(self, sql):
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result

    # 查询所有的
    def query_all(self, sql):
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        return result

    # 查询指定N条
    def query_many(self, sql, n):
        self.cursor.execute(sql)
        result = self.cursor.fetchmany(n)
        return result

    # 插入一条数据
    def execute_one(self, sql):
        self.cursor.execute(sql)
        self.conn.commit()
        last_message = self.cursor.lastrowid
        return 'done'

    # insert多条数据
    def insert_many(self, sql, data):
        self.cursor.executemany()
        self.conn.commit()
        return "done"

    '''
    data的格式
    data = [
    ('july', '147'),
    ('june', '258'),
    ('marin', '369')
]
    '''

    def __del__(self):  # 析构方法
        self.conn.close()
        self.cursor.close()

    # 获取最新插入数据的ID
    def get_last_message(self):
        last_message = self.cursor.lastrowid
        return last_message


if __name__ == '__main__':
    db = Database()
    import json

    sql_insert = "INSERT into member_auth_status (id,org_id,member_id,type,status,is_del) VALUES (1009,'1008328420328325120','1082334140434133012',8,3,0)"
    print("新增")
    db.execute_one(sql_insert)  # 新增

    sql = "update member_auth_status set status=%s where id=%s;"
    print("修改")
    db.cursor.execute(sql, ["10", "1009"])  # 修改

    sql_del = 'delete from member_auth_status where id=1009;'
    print("删除")
    db.execute_one(sql_del)  # 删除
